package com.z1.core.kvutils.rdbms;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;

import com.z1.core.io.ObjectByte;
import com.z1.core.kvutils.KvFactoryConst;
import com.z1.core.kvutils.KvObject;

public final class KvRdbms implements KvObject {
	
	private final Logger logger = Logger.getLogger(getClass());
	
	private DataSource dataSource;
	public void setDataSource(DataSource dataSource) {
		this.dataSource = dataSource;
		if (null != dataSource) {
			initTable();
		}
	}
	
	private KvRdbms() {
		if (null != dataSource) {
			initTable();
		}
	}
	
	private void initTable() {
		if (null == dataSource) {
			throw new RuntimeException("KvRdbms dataSource is null.");
		}
		
		Connection conn = null;
		Statement stmt = null;
		try {
			conn = dataSource.getConnection();
			DatabaseMetaData meta = conn.getMetaData();
			logger.info("KvRdbms Driver:" + meta.getDriverName());
			logger.info("KvRdbms Url:" + meta.getURL());
			
			final String INIT_SQL = new StringBuffer()
				.append("CREATE TABLE IF NOT EXISTS `tbl_config`  (\n")
				.append("`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '编号',\n")
				.append("`group` varchar(100) NOT NULL COMMENT '分组',\n")
				.append("`name` varchar(100) NOT NULL COMMENT '名称',\n")
				.append("`value` blob COMMENT '内容',\n")
				.append("PRIMARY KEY (`id`) USING BTREE,\n")
				.append("INDEX `index_config_group`(`group`) USING BTREE COMMENT '配置分组索引',\n")
				.append("INDEX `index_config_name`(`name`) USING BTREE COMMENT '配置名称索引'\n")
				.append(") ENGINE = InnoDB AUTO_INCREMENT = 1 COMMENT = '配置信息' ")
				.toString();
			logger.debug("init kvRdbms");
			logger.debug(INIT_SQL);
			
			stmt = conn.createStatement();
			stmt.execute(INIT_SQL);
		}
		catch (Throwable e) {
			throw new RuntimeException(e);
		}
		finally {
			try {
				if (null != stmt) { stmt.close(); }
				if (null != conn) { conn.close(); }
			}
			catch (Throwable e) { }
		}
	}
	
	@Override
	public void set(String group, String key, Object value) {
		if (null == dataSource) {
			return;
		} 
		if (StringUtils.isBlank(key)) {
			return;
		}
		if (StringUtils.isBlank(group)) {
			group = GROUP_DEFAULT;
		}
		
		Connection conn = null;
		PreparedStatement stmt = null;
		try {
			conn = dataSource.getConnection();
			
			Object existObj = get(group, key, null);
			
			if (null == existObj) {
				final String SET_SQL = "INSERT INTO `tbl_config`(`group`,`name`,`value`) VALUES(?,?,?)";
				logger.debug("kvRdbms set(group:" + group + ";key:" + key + ";value:" + (null == value ? "null" : value) + ")");
				logger.debug(SET_SQL);
				
				stmt = conn.prepareStatement(SET_SQL);
				stmt.setObject(1, group);
				stmt.setObject(2, key);
				stmt.setObject(3, null == value ? null : ObjectByte.toBytes(value));
			} else {
				final String SET_SQL = "UPDATE `tbl_config` set `value`=? WHERE `group`=? AND `name`=?";
				logger.debug("kvRdbms set(group:" + group + ";key:" + key + ";value:" + (null == value ? "null" : value) + ")");
				logger.debug(SET_SQL);
				
				stmt = conn.prepareStatement(SET_SQL);
				stmt.setObject(1, null == value ? null : ObjectByte.toBytes(value));
				stmt.setObject(2, group);
				stmt.setObject(3, key);
			}
			stmt.executeUpdate();
		}
		catch (Throwable e) {
			throw new RuntimeException(e);
		}
		finally {
			try {
				if (null != stmt) { stmt.close(); }
				if (null != conn) { conn.close(); }
			}
			catch (Throwable e) { }
		}
	}
	
	@Override
	public List<String> groups() {
		List<String> groups = new ArrayList<String>();
		if (null == dataSource) {
			return groups;
		} 
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			final String GROUP_SQL = "SELECT DISTINCT(`group`) FROM `tbl_config`";
			logger.debug("kvRdbms groups");
			logger.debug(GROUP_SQL);
			
			conn = dataSource.getConnection();
			stmt = conn.prepareStatement(GROUP_SQL);
			rs = stmt.executeQuery();
			while (rs.next()) {
				Object group = rs.getObject(1);
				if (null != group) {
					groups.add(group.toString());
				}
			}
		}
		catch (Throwable e) {
			throw new RuntimeException(e);
		}
		finally {
			try {
				if (null != rs) { rs.close(); }
				if (null != stmt) { stmt.close(); }
				if (null != conn) { conn.close(); }
			}
			catch (Throwable e) { }
		}
		return groups;
	}

	@Override
	public Map<String, Object> get(String group) {
		Map<String, Object> map = new HashMap<String, Object>();
		if (null == dataSource) {
			return map;
		} 
		if (null == group) {
			group = GROUP_DEFAULT;
		}
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			final String GET_SQL = "SELECT `name`,`value` FROM `tbl_config` WHERE `group`=?";
			logger.debug("kvRdbms get(group:" + group + ")");
			logger.debug(GET_SQL);
			
			conn = dataSource.getConnection();
			stmt = conn.prepareStatement(GET_SQL);
			stmt.setObject(1, group);
			rs = stmt.executeQuery();
			while (rs.next()) {
				String key = rs.getString(1);
				byte[] b = rs.getBytes(2);
				map.put(key, null == b ? null : ObjectByte.toObject(b));
			}
		}
		catch (Throwable e) {
			throw new RuntimeException(e);
		}
		finally {
			try {
				if (null != rs) { rs.close(); }
				if (null != stmt) { stmt.close(); }
				if (null != conn) { conn.close(); }
			}
			catch (Throwable e) { }
		}
		return map;
	}
	
	@Override
	public int count(String group) {
		if (null == dataSource) {
			return 0;
		} 
		if (null == group) {
			group = GROUP_DEFAULT;
		}
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			final String COUNT_SQL = "SELECT count(0) FROM `tbl_config` WHERE `group`=?";
			logger.debug("kvRdbms count(group:" + group + ")");
			logger.debug(COUNT_SQL);
			
			conn = dataSource.getConnection();
			stmt = conn.prepareStatement(COUNT_SQL);
			stmt.setObject(1, group);
			rs = stmt.executeQuery();
			while (rs.next()) {
				return rs.getInt(1);
			}
		}
		catch (Throwable e) {
			throw new RuntimeException(e);
		}
		finally {
			try {
				if (null != rs) { rs.close(); }
				if (null != stmt) { stmt.close(); }
				if (null != conn) { conn.close(); }
			}
			catch (Throwable e) { }
		}
		return 0;
	}
	
	@Override
	public Object get(String group, String key) {
		return get(group, key, null);
	}
	
	private Object get(String group, String key, Connection conn) {
		if (StringUtils.isBlank(key)) {
			return null;
		}
		if (null == group) {
			group = GROUP_DEFAULT;
		}
		
		Connection localConn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			final String GET_SQL = "SELECT `value` FROM `tbl_config` WHERE `group`=? AND `name`=?";
			logger.debug("kvRdbms get(group:" + group + ";key:" + key + ")");
			logger.debug(GET_SQL);
			
			if (null == conn) {
				if (null == dataSource) {
					return null;
				} 
				localConn = dataSource.getConnection();
				stmt = localConn.prepareStatement(GET_SQL);
			}
			else {
				stmt = conn.prepareStatement(GET_SQL);
			}
			stmt.setObject(1, group);
			stmt.setObject(2, key);
			rs = stmt.executeQuery();
			while (rs.next()) {
				byte[] b = rs.getBytes(1);
				return null == b ? null : ObjectByte.toObject(b);
			}
		}
		catch (Throwable e) {
			throw new RuntimeException(e);
		}
		finally {
			try {
				if (null != rs) { rs.close(); }
				if (null != stmt) { stmt.close(); }
				if (null != localConn) { localConn.close(); }
			}
			catch (Throwable e) { }
		}
		return null;
	}
	
	@Override
	public void remove(String group, String key) {
		if (null == dataSource) {
			return;
		} 
		if (null == group) {
			group = GROUP_DEFAULT;
		}
		Connection conn = null;
		PreparedStatement stmt = null;
		try {
			final String REMOVE_SQL = "DELETE FROM `tbl_config` WHERE `group`=? AND `name`=?";
			logger.debug("kvRdbms remove(group:" + group + ";key:" + key + ")");
			logger.debug(REMOVE_SQL);
			
			conn = dataSource.getConnection();
			stmt = conn.prepareStatement(REMOVE_SQL);
			stmt.setObject(1, group);
			stmt.setObject(2, key);
			stmt.executeUpdate();
		}
		catch (Throwable e) {
			throw new RuntimeException(e);
		}
		finally {
			try {
				if (null != stmt) { stmt.close(); }
				if (null != conn) { conn.close(); }
			}
			catch (Throwable e) { }
		}
	}
	
	@Override
	public String type() {
		return KvFactoryConst.KV_RDBMS;
	}

	private static KvRdbms kvObject = null;
	public static final KvRdbms getInstance() {
		if (null == kvObject) {
			kvObject = new KvRdbms();
		}
		return kvObject;
	}
	
}
